	/**
	 * Review数据模型模块
	 * model\sqlite\review.js
	 */

	//导入SQLite3模块
	const sqlite3 = require("sqlite3").verbose();
	//导入工具模块
	const util = require("../../common/util");
	//导入配置模块
	const config = require("../../common/config");

	class ReviewDB {
	  /**
	   * @constructor
	   * @private
	   */
	  constructor() {
	    this.dbFile = config.dbFile;
	    this.instance = null;
	    this.db = null;
	  }

	  /**
	   * 创建ReviewDB对象
	   * @returns {ReviewDB} ReviewDB实例
	   */
	  static getInstance() {
	    if (!this.instance) {
	      this.instance = new ReviewDB();
	    }
	    return this.instance;
	  }

	  /**
	   * 连接数据库
	   * @returns {Promise}
	   */
	  connect() {
	    return new Promise((resolve, reject) => {
	      this.db = new sqlite3.Database(this.dbFile, (err) => {
	        if (err) {
	          util.err(err);
	          reject(err);
	        } else {
	          resolve("connect ok.");
	        }
	      });
	    });
	  }
	  /**
	   * 关闭数据库
	   * @returns  {Promise}
	   */
	  close() {
	    return new Promise((resolve, reject) => {
	      this.db.close((err) => {
	        if (err) {
	          util.err(err);
	          reject(err);
	        } else {
	          resolve("close ok.");
	        }
	      });
	    });
	  }

	  /**
	   * 获取指定ID的评论信息
	   * @param {Number} id 评论id
	   * @returns  {Promise}
	   */
	  find(id) {
	    return new Promise((resolve, reject) => {
	      let sql = `SELECT 
                     a.id AS id,
                     a.book_id AS book_id,
                     a.user_id AS user_id,
                     a.title AS title,
                     a.content AS content,
                     a.useful AS useful,
                     a.useless AS useless,
                     a.created_time AS created_time,
                     a.updated_time AS updated_time,
                     b.title AS book_title,
                     b.pic AS pic,
                     b.local_pic AS local_pic,
                     b.author AS author,
                     b.publisher AS publisher, 
                     b.producer AS producer,
                     b.subtitle AS subtitle, 
                     b.originalTitle AS originalTitle, 
                     b.translator AS translator, 
                     b.pubdate AS pubdate, 
                     b.pages AS pages, 
                     b.price AS price, 
                     b.binding AS binding, 
                     b.series AS series, 
                     b.isbn AS isbn, 
                     b.intro AS intro, 
                     b.doubanId AS doubanId,
                     c.username AS username,
                     c.nickname AS nickname
                   FROM reviews AS a 
                   LEFT JOIN books AS b ON a.book_id = b.id 
                   LEFT JOIN users AS c ON a.user_id = c.id
                   WHERE a.id = ?
                `;
	      let params = [id];
	      this.db.get(sql, params, (err, result) => {
	        if (err) {
	          util.err(err);
	          reject(err);
	        } else {
	          resolve(result);
	        }
	      });
	    });
	  }

	  /**
	   * 获取指定用户的书架信息列表
	   * @param {Number} bookId 书籍ID
	   * @param {String} orderBy 排序字段
	   * @param {String} sort 排序方式
	   * @param {Number} limit 数量
	   * @param {Number} offset 开始
	   * @returns  {Promise}
	   */
	  findAll(bookId, orderBy = "id", sort = "desc", limit = -1, offset = -1) {
	    return new Promise((resolve, reject) => {
	      let sql = `SELECT 
                    a.id AS id,
                    a.book_id AS book_id,
                    a.user_id AS user_id,
                    a.title AS title,
                    a.content AS content,
                    a.useful AS useful,
                    a.useless AS useless,
                    a.created_time AS created_time,
                    a.updated_time AS updated_time,
                    b.title AS book_title,
                    b.pic AS pic,
                    b.local_pic AS local_pic,
                    b.author AS author,
                    b.publisher AS publisher, 
                    b.producer AS producer,
                    b.subtitle AS subtitle, 
                    b.originalTitle AS originalTitle, 
                    b.translator AS translator, 
                    b.pubdate AS pubdate, 
                    b.pages AS pages, 
                    b.price AS price, 
                    b.binding AS binding, 
                    b.series AS series, 
                    b.isbn AS isbn, 
                    b.intro AS intro, 
                    b.doubanId AS doubanId,
                    c.username AS username,
                    c.nickname AS nickname
                FROM reviews AS a 
                LEFT JOIN books AS b ON a.book_id = b.id 
                LEFT JOIN users AS c ON a.user_id = c.id
                WHERE a.book_id = ?
                `;
	      let params = [bookId];
	      if (limit === -1) {
	        sql += ` ORDER BY ${orderBy} ${sort}`;
	      } else if (offset === -1) {
	        sql += ` ORDER BY ${orderBy} ${sort} LIMIT ?`;
	        params.push(limit);
	      } else {
	        sql += ` ORDER BY ${orderBy} ${sort} LIMIT ? OFFSET ?`;
	        params.push(limit);
	        params.push(offset);
	      }
	      this.db.all(sql, params, (err, result) => {
	        if (err) {
	          util.err(err);
	          reject(err);
	        } else {
	          resolve(result);
	        }
	      });
	    });
	  }

	  /**
	   * 新增
	   * @param {Object} review 评论数据
	   * @returns  {Promise}
	   */
	  add(review) {
	    return new Promise((resolve, reject) => {
	      let sql = `INSERT INTO reviews (
        user_id, book_id, title, content, created_time, updated_time
    ) VALUES (?, ?, ?, ?, ?, ?);`;
	      let params = [
	        review.userId,
	        review.bookId,
	        review.title,
	        review.content,
	        review.createdTime,
	        review.updatedTime,
	      ];

	      this.db.run(sql, params, function (err, result) {
	        if (err) {
	          util.err(err);
	          reject(err);
	        } else {
	          resolve(this.lastID); //插入的数据的自增ID
	        }
	      });
	    });
	  }

	  /**
	   * 修改
	   * @param {Object} review 评论数据
	   * @returns  {Promise}
	   */
	  update(review) {
	    return new Promise((resolve, reject) => {
	      let sql = `UPDATE reviews SET 
      title = ?, content = ?, updated_time = ?
      WHERE id = ?;`;
	      let params = [
	        review.title,
	        review.content,
	        review.updatedTime,
	        review.id,
	      ];

	      this.db.run(sql, params, function (err, result) {
	        if (err) {
	          util.err(err);
	          reject(err);
	        } else {
	          resolve(this.changes); //影响的记录数
	        }
	      });
	    });
	  }

	  /**
	   * 删除
	   * @param {Number} reviewId 评论id
	   * @returns  {Promise}
	   */
	  remove(reviewId) {
	    return new Promise((resolve, reject) => {
	      let sql = "DELETE FROM reviews WHERE id = ?";
	      let params = [reviewId];
	      this.db.run(sql, params, function (err, result) {
	        if (err) {
	          util.err(err);
	          reject(err);
	        } else {
	          resolve(this.changes); //影响的记录数
	        }
	      });
	    });
	  }

	  /**
	   * 获取指定书籍的评论数量
	   * @returns  {Promise}
	   */
	  getCount(bookId) {
	    return new Promise((resolve, reject) => {
	      let sql = "SELECT count(1) AS total FROM reviews WHERE book_id = ?";
	      let params = [bookId];
	      this.db.get(sql, params, function (err, result) {
	        if (err) {
	          util.err(err);
	          reject(err);
	        } else {
	          resolve(result);
	        }
	      });
	    });
	  }

	  /**
	   * 更新评论有用
	   * @param {Number} reviewId 评论id
	   * @returns {Promise}
	   */
	  updateUseful(reviewId) {
	    return new Promise((resolve, reject) => {
	      let sql = `UPDATE reviews SET useful = useful + 1 WHERE id = ?;`;
	      let params = [reviewId];

	      this.db.run(sql, params, function (err, result) {
	        if (err) {
	          util.err(err);
	          reject(err);
	        } else {
	          resolve(this.changes); //影响的记录数
	        }
	      });
	    });
	  }

	  /**
	   * 更新评论无用
	   * @param {Number} reviewId 评论id
	   * @returns {Promise}
	   */
	  updateUseless(reviewId) {
	    return new Promise((resolve, reject) => {
	      let sql = `UPDATE reviews SET useless = useless + 1 WHERE id = ?;`;
	      let params = [reviewId];

	      this.db.run(sql, params, function (err, result) {
	        if (err) {
	          util.err(err);
	          reject(err);
	        } else {
	          resolve(this.changes); //影响的记录数
	        }
	      });
	    });
	  }

	  /**
	   * 获取指定用户的评论信息列表
	   * @param {Number} userId 书籍ID
	   * @param {String} orderBy 排序字段
	   * @param {String} sort 排序方式
	   * @param {Number} limit 数量
	   * @param {Number} offset 开始
	   * @returns {Promise}
	   */
	  findAllByUserId(userId, orderBy = "id", sort = "desc", limit = -1, offset = -1) {
	    return new Promise((resolve, reject) => {
	      let sql = `SELECT 
             a.id AS id,
             a.book_id AS book_id,
             a.user_id AS user_id,
             a.title AS title,
             a.content AS content,
             a.useful AS useful,
             a.useless AS useless,
             a.created_time AS created_time,
             a.updated_time AS updated_time,
             b.title AS book_title,
             b.pic AS pic,
             b.local_pic AS local_pic,
             b.author AS author,
             b.publisher AS publisher, 
             b.producer AS producer,
             b.subtitle AS subtitle, 
             b.originalTitle AS originalTitle, 
             b.translator AS translator, 
             b.pubdate AS pubdate, 
             b.pages AS pages, 
             b.price AS price, 
             b.binding AS binding, 
             b.series AS series, 
             b.isbn AS isbn, 
             b.intro AS intro, 
             b.doubanId AS doubanId,
             c.username AS username,
             c.nickname AS nickname
           FROM reviews AS a 
           LEFT JOIN books AS b ON a.book_id = b.id 
           LEFT JOIN users AS c ON a.user_id = c.id
           WHERE a.user_id = ?
           `;
	      let params = [userId];
	      if (limit === -1) {
	        sql += ` ORDER BY ${orderBy} ${sort}`;
	      } else if (offset === -1) {
	        sql += ` ORDER BY ${orderBy} ${sort} LIMIT ?`;
	        params.push(limit);
	      } else {
	        sql += ` ORDER BY ${orderBy} ${sort} LIMIT ? OFFSET ?`;
	        params.push(limit);
	        params.push(offset);
	      }
	      this.db.all(sql, params, (err, result) => {
	        if (err) {
	          util.err(err);
	          reject(err);
	        } else {
	          resolve(result);
	        }
	      });
	    });
	  }
	  /**
	   * 获取指定用户的评论数量
	   * @param {Number} userId
	   * @returns {Promise}
	   */
	  getCountByUserId(userId) {
	    return new Promise((resolve, reject) => {
	      let sql = "SELECT count(1) AS total FROM reviews WHERE user_id = ?";
	      let params = [userId];
	      this.db.get(sql, params, function (err, result) {
	        if (err) {
	          util.err(err);
	          reject(err);
	        } else {
	          resolve(result);
	        }
	      });
	    });
	  }

	}

	module.exports = ReviewDB;